import pymysql
import time
from concurrent.futures import ThreadPoolExecutor, as_completed
import logging
import traceback
from datetime import datetime
import re
import threading
from queue import Queue
from dbutils.pooled_db import PooledDB

# 在文件开头添加配置项
CONFIG = {
    # 数据库连接池配置
    'DB_POOL': {
        'maxconnections': 20,    # 最大连接数
        'mincached': 5,         # 初始连接数
        'maxcached': 10,        # 最大空闲连接
        'maxshared': 10         # 最大共享连接数
    },
    # 线程池配置
    'THREAD_POOL': {
        'goods_workers': 6,     # 商品处理线程数
        'sku_workers': 4        # SKU处理线程数
    }
}



# 日志文件配置
log_file = f"migration_errors_{datetime.now().strftime('%Y%m%d_%H%M%S')}.log"
logging.basicConfig(
    filename=log_file,
    level=logging.ERROR,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 数据库连接配置
source_db_config = {
    "host": "127.0.0.1",  # ECShop 数据库地址
    "user": "root",
    "password": "123456",
    "database": "maigeyin",
    "autocommit": True,
}

target_db_config = {
    "host": "127.0.0.1",  # FastAdmin 数据库地址
    "user": "root",
    "password": "123456",
    "database": "5i40",
    "autocommit": True,
}


# 修改连接池创建部分
source_pool = PooledDB(
    creator=pymysql,
    maxconnections=CONFIG['DB_POOL']['maxconnections'],
    mincached=CONFIG['DB_POOL']['mincached'],
    maxcached=CONFIG['DB_POOL']['maxcached'],
    maxshared=CONFIG['DB_POOL']['maxshared'],
    blocking=True,
    maxusage=10000,
    setsession=[],
    ping=1,
    **source_db_config
)

target_pool = PooledDB(
    creator=pymysql,
    maxconnections=CONFIG['DB_POOL']['maxconnections'],
    mincached=CONFIG['DB_POOL']['mincached'],
    maxcached=CONFIG['DB_POOL']['maxcached'],
    maxshared=CONFIG['DB_POOL']['maxshared'],
    blocking=True,
    maxusage=10000,
    setsession=[],
    ping=1,
    **target_db_config
)

# 修改连接队列大小
source_conn_queue = Queue(maxsize=CONFIG['DB_POOL']['maxconnections'])
target_conn_queue = Queue(maxsize=CONFIG['DB_POOL']['maxconnections'])


def init_connection_pools():
    """初始化连接池"""
    try:
        # 预先创建一些连接放入队列
        for _ in range(5):
            source_conn_queue.put(source_pool.connection())
            target_conn_queue.put(target_pool.connection())
    except Exception as e:
        logging.error(f"初始化连接池失败: {str(e)}")
        raise

def get_source_connection():
    """从队列获取源数据库连接"""
    try:
        return source_conn_queue.get(timeout=5)
    except:
        return source_pool.connection()

def get_target_connection():
    """从队列获取目标数据库连接"""
    try:
        return target_conn_queue.get(timeout=5)
    except:
        return target_pool.connection()

def return_connections(source_conn, target_conn):
    """归还连接到队列"""
    if source_conn:
        try:
            source_conn_queue.put(source_conn, timeout=1)
        except:
            source_conn.close()
    if target_conn:
        try:
            target_conn_queue.put(target_conn, timeout=1)
        except:
            target_conn.close()

def process_image_path(path):
    """处理图片路径，添加或替换域名"""
    if not path:
        return ""
    
    # 替换旧域名
    if '5izazhi.5izazhi.com' in path:
        path = path.replace('5izazhi.5izazhi.com', 'zimg.zazhiejia.com')
    
    # 如果已经是完整的 URL，直接返回
    if path.startswith(('http://', 'https://')):
        return path
    
    # 移除开头的斜杠
    path = path.lstrip('/')
    
    # 添加域名
    return f"https://zimg.zazhiejia.com/{path}"

def process_content_images(content):
    """处理内容中的图片路径"""
    if not content:
        return content
    
    # 处理 src="path" 的情况
    pattern = r'src=["\']((?!https?://)[^"\']+)["\']'
    
    def replace_path(match):
        path = match.group(1)
        new_path = process_image_path(path)
        return f'src="{new_path}"'
    
    # 替换旧域名
    content = content.replace('5izazhi.5izazhi.com', 'zimg.zazhiejia.com')
    
    # 处理相对路径
    return re.sub(pattern, replace_path, content)

def get_subscription_years(attr_value):
    """根据属性值获取订阅年份"""
    if not attr_value:
        return 1
    
    attr_value = str(attr_value).lower()
    if "两年" in attr_value or "2年" in attr_value:
        return 2
    elif "三年" in attr_value or "3年" in attr_value:
        return 3
    return 1

def clear_target_tables():
    """清空目标数据库相关表数据"""
    print("清空目标数据库相关表数据...")
    target_conn = None
    target_cursor = None
    try:
        target_conn = get_target_connection()
        target_cursor = target_conn.cursor()
        
        # 先删除外键约束（如果有的话）
        disable_foreign_key_checks = "SET FOREIGN_KEY_CHECKS = 0"
        enable_foreign_key_checks = "SET FOREIGN_KEY_CHECKS = 1"
        
        target_cursor.execute(disable_foreign_key_checks)
        
        tables = [
            "fa_shop_category", 
            "fa_shop_goods", 
            "fa_shop_goods_sku", 
            "fa_shop_goods_sku_spec", 
            "fa_shop_spec", 
            "fa_shop_spec_value"
        ]
        
        for table in tables:
            # 清空表并重置自增ID
            truncate_sql = f"TRUNCATE TABLE {table}"
            alter_sql = f"ALTER TABLE {table} AUTO_INCREMENT = 1"
            
            target_cursor.execute(truncate_sql)
            target_cursor.execute(alter_sql)
            print(f"已清空表 {table} 并重置自增ID")
        
        target_cursor.execute(enable_foreign_key_checks)
        target_conn.commit()
        
    except Exception as e:
        logging.error(f"清空目标表时发生错误: {e}")
        print(f"清空目标表时发生错误: {e}")
    finally:
        if target_cursor:
            target_cursor.close()
        if target_conn:
            return_connections(None, target_conn)

def create_default_category():
    """创建默认分类（ID=999）"""
    print("创建默认分类...")
    target_conn = None
    target_cursor = None
    try:
        target_conn = get_target_connection()
        target_cursor = target_conn.cursor()
        
        # 先检查是否已存在ID=999的记录
        check_sql = "SELECT id FROM fa_shop_category WHERE id = 999"
        target_cursor.execute(check_sql)
        if target_cursor.fetchone():
            print("ID=999的默认分类已存在")
            return
        
        # 使用指定的ID插入默认分类
        sql = """
            INSERT INTO fa_shop_category 
            (id, name, pid, description, isnav, weigh, status, createtime, updatetime)
            VALUES (999, '其他杂志', 0, '未匹配分类', 1, 0, 'normal', %s, %s)
        """
        current_time = int(time.time())
        target_cursor.execute(sql, (current_time, current_time))
        target_conn.commit()
        print("默认分类创建成功！")
        
    except Exception as e:
        logging.error(f"创建默认分类时发生错误: {e}")
        print(f"创建默认分类时发生错误: {e}")
    finally:
        if target_cursor:
            target_cursor.close()
        if target_conn:
            return_connections(None, target_conn)
            
def migrate_categories():
    """迁移分类数据"""
    print("迁移分类数据...")
    source_conn = None
    target_conn = None
    category_map = {}  # 分类映射表
    try:
        source_conn = get_source_connection()
        target_conn = get_target_connection()
        
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)
        
        # 获取所有分类数据并按父ID排序
        source_cursor.execute("""
            SELECT * FROM ecs_category 
            ORDER BY parent_id ASC, sort_order ASC
        """)
        categories = source_cursor.fetchall()
        
        # 先处理顶级分类
        for category in categories:
            if category['parent_id'] == 0:
                new_id = insert_category(category, 0, target_cursor)
                if new_id:
                    category_map[category['cat_id']] = new_id
        
        # 再处理子分类
        for category in categories:
            if category['parent_id'] != 0:
                # 获取新的父级ID
                new_parent_id = category_map.get(category['parent_id'], 0)
                new_id = insert_category(category, new_parent_id, target_cursor)
                if new_id:
                    category_map[category['cat_id']] = new_id
        
        target_conn.commit()
        print(f"分类数据迁移完成！共迁移 {len(category_map)} 个分类")
        return category_map
    except Exception as e:
        logging.error(f"迁移分类时发生错误: {str(e)}")
        print(f"迁移分类时发生错误: {str(e)}")
        return {}
    finally:
        if source_cursor:
            source_cursor.close()
        if target_cursor:
            target_cursor.close()
        return_connections(source_conn, target_conn)

def insert_category(category, new_parent_id, target_cursor):
    """插入单个分类"""
    try:
        current_time = int(time.time())
        
        # 处理分类图片
        image = process_image_path(category.get('type_img', ''))
        icon = process_image_path(category.get('cat_ico', ''))
        
        # 设置flag
        flags = []
        if category.get('show_in_index', 0):
            flags.append('index')
        if category.get('is_top_show', 0):
            flags.append('hot')
        flag = ','.join(flags)
        
        # 插入数据
        sql = """
            INSERT INTO fa_shop_category (
                pid, name, nickname, keywords, description, image, icon,
                isnav, flag, weigh, status, createtime, updatetime
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
        """
        params = (
            new_parent_id,                           # pid
            category['cat_name'],                    # name
            category.get('path_name', ''),           # nickname
            category.get('keywords', ''),            # keywords
            category.get('cat_desc', ''),            # description
            image,                                   # image
            icon,                                    # icon
            1 if category.get('show_in_nav', 0) else 0,  # isnav
            flag,                                    # flag
            category.get('sort_order', 0),           # weigh
            'normal' if category.get('is_show', 1) else 'hidden',  # status
            current_time,                            # createtime
            current_time                             # updatetime
        )
        
        target_cursor.execute(sql, params)
        target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
        result = target_cursor.fetchone()
        return result['id']
        
    except Exception as e:
        logging.error(f"插入分类 {category['cat_name']} 时发生错误: {str(e)}")
        print(f"插入分类 {category['cat_name']} 时发生错误: {str(e)}")
        return None



def process_single_sku(product, new_goods_id, base_price, market_price, sku_map):
    """处理单个 SKU 的函数"""
    source_conn = None
    target_conn = None
    source_cursor = None
    target_cursor = None
    goods_sn = None
    
    try:
        # 获取连接
        source_conn = get_source_connection()
        target_conn = get_target_connection()
        
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)

        goods_sn = product['product_sn']
        product_number = product['product_number']
        goods_attr = product['goods_attr']

        # SKU ID 处理
        if not goods_attr:
            sku_id = "0"
        else:
            try:
                sku_attrs = [attr.strip() for attr in goods_attr.split("|") if attr.strip()]
                sku_ids = []
                for attr in sku_attrs:
                    try:
                        attr_id = int(attr)
                        if attr_id in sku_map:
                            sku_ids.append(str(sku_map[attr_id]))
                    except (ValueError, TypeError):
                        continue
                sku_id = ",".join(sku_ids) if sku_ids else "0"
            except Exception:
                sku_id = "0"

        # 获取订阅年份
        years = 1
        if goods_attr and "|" in goods_attr:
            source_cursor.execute("""
                SELECT attr_value 
                FROM ecs_goods_attr 
                WHERE goods_attr_id = %s
            """, (goods_attr.split("|")[1],))
            attr_result = source_cursor.fetchone()
            years = get_subscription_years(attr_result['attr_value'] if attr_result else None)

        # 计算最终价格和市场价
        final_price = float(base_price) * years
        final_market_price = float(market_price) * years

        # 插入 SKU 数据
        sku_sql = """
            INSERT INTO fa_shop_goods_sku 
            (goods_id, goods_sn, sku_id, price, marketprice, stocks, createtime, updatetime)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """
        current_time = int(time.time())
        sku_params = (new_goods_id, goods_sn, sku_id, final_price, 
                     final_market_price, product_number, current_time, current_time)
        target_cursor.execute(sku_sql, sku_params)
        target_conn.commit()

    except Exception as e:
        error_msg = f"处理 SKU 时发生错误: {str(e)}"
        if goods_sn:
            error_msg += f", goods_sn: {goods_sn}"
        logging.error(error_msg)
        print(error_msg)
        raise
    finally:
        if source_cursor:
            source_cursor.close()
        if target_cursor:
            target_cursor.close()
        # 归还连接到连接池
        return_connections(source_conn, target_conn)

def migrate_sku(old_goods_id, new_goods_id):
    """处理规格和 SKU 数据"""
    source_conn = None
    target_conn = None
    try:
        source_conn = get_source_connection()
        target_conn = get_target_connection()
        
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)

        # 获取商品基础价格和市场价
        source_cursor.execute("SELECT shop_price, market_price FROM ecs_goods WHERE goods_id = %s", (old_goods_id,))
        goods_price = source_cursor.fetchone()
        base_price = goods_price['shop_price']
        market_price = goods_price['market_price']

        # 获取商品的规格属性
        source_cursor.execute("""
            SELECT ga.*, a.attr_name 
            FROM ecs_goods_attr ga 
            LEFT JOIN ecs_attribute a ON ga.attr_id = a.attr_id 
            WHERE ga.goods_id = %s
        """, (old_goods_id,))
        goods_attrs = source_cursor.fetchall()

        spec_map = {}  # 记录规格模板
        sku_map = {}  # 记录 SKU 数据

        # 处理规格模板
        for attr in goods_attrs:
            attr_id = attr['attr_id']
            attr_value = attr['attr_value']
            attr_name = attr['attr_name']

            # 确定规格名称
            spec_name = None
            if "时间" in attr_name or "日期" in attr_name:
                spec_name = "起刊时间"
            elif "年" in attr_name or "订阅" in attr_name:
                spec_name = "订阅年份"
            else:
                spec_name = "起刊时间"  # 默认作为起刊时间

            # 确保规格模板存在
            if attr_id not in spec_map:
                spec_sql = """
                    INSERT INTO fa_shop_spec 
                    (name, createtime, updatetime) 
                    VALUES (%s, %s, %s)
                """
                current_time = int(time.time())
                spec_params = (spec_name, current_time, current_time)
                target_cursor.execute(spec_sql, spec_params)
                target_conn.commit()

                target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
                spec_map[attr_id] = target_cursor.fetchone()['id']

            # 插入规格值
            spec_value_sql = """
                INSERT INTO fa_shop_spec_value 
                (spec_id, value, createtime, updatetime) 
                VALUES (%s, %s, %s, %s)
            """
            current_time = int(time.time())
            spec_value_params = (spec_map[attr_id], attr_value, current_time, current_time)
            target_cursor.execute(spec_value_sql, spec_value_params)
            target_conn.commit()

            target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
            spec_value_id = target_cursor.fetchone()['id']

            # 插入 fa_shop_goods_sku_spec 表
            sku_spec_sql = """
                INSERT INTO fa_shop_goods_sku_spec 
                (goods_id, spec_id, spec_value_id, createtime, updatetime) 
                VALUES (%s, %s, %s, %s, %s)
            """
            current_time = int(time.time())
            sku_spec_params = (new_goods_id, spec_map[attr_id], spec_value_id, current_time, current_time)
            target_cursor.execute(sku_spec_sql, sku_spec_params)
            target_conn.commit()

            sku_map[attr['goods_attr_id']] = spec_value_id

        # 处理 SKU - 使用多线程
        source_cursor.execute("SELECT * FROM ecs_products WHERE goods_id = %s", (old_goods_id,))
        products = source_cursor.fetchall()

        # 减少线程池大小
        with ThreadPoolExecutor(max_workers=CONFIG['THREAD_POOL']['sku_workers']) as executor:
            futures = [
                executor.submit(
                    process_single_sku,
                    product,
                    new_goods_id,
                    base_price,
                    market_price,
                    sku_map
                )
                for product in products
            ]

            for future in as_completed(futures):
                try:
                    future.result()
                except Exception as e:
                    logging.error(f"SKU 处理任务失败: {str(e)}")
                    print(f"SKU 处理任务失败: {str(e)}")

    finally:
        if source_cursor:
            source_cursor.close()
        if target_cursor:
            target_cursor.close()
        if source_conn:
            return_connections(source_conn, None)
        if target_conn:
            return_connections(None, target_conn)

def process_single_goods(goods, category_map):
    """处理单个商品的函数"""
    target_conn = None
    target_cursor = None
    try:
        target_conn = get_target_connection()
        target_cursor = target_conn.cursor(pymysql.cursors.DictCursor)

        goods_id = goods['goods_id']
        cat_id = goods['cat_id']
        category_id = category_map.get(cat_id, 999)

        # 处理图片路径
        goods_thumb = process_image_path(goods['goods_thumb'])
        goods_img = process_image_path(goods['goods_img'])
        original_img = process_image_path(goods['original_img'])

        # 替换描述中的特定文本
        description = goods['goods_brief']
        if description:
            description = description.replace('杂志E家', '麦格音').replace('杂志2007', '麦格音')

        # 处理关键词
        keywords = []
        if goods.get('keywords'):
            keywords.append(goods['keywords'])
        if goods.get('introduce'):
            keywords.append(goods['introduce'])
        if goods.get('recommended'):
            keywords.append(goods['recommended'])
        if goods.get('shopdian'):
            keywords.append(goods['shopdian'])
        keywords_str = ','.join(filter(None, keywords))

        # 处理商品详情，包括图片路径
        content = goods.get('goods_desc', '')
        if content:
            content = content.replace('杂志E家', '麦格音').replace('杂志2007', '麦格音')
            content = process_content_images(content)

        # 处理其他可能包含图片路径的字段
        goods_office = process_image_path(goods.get('goods_office', ''))
        goods_trial = process_image_path(goods.get('goods_trial', ''))
        goods_kind = goods.get('goods_kind', '')
        pinyin = goods.get('pinyin', '')

        # 插入商品数据
        goods_sql = """
            INSERT INTO fa_shop_goods 
            (category_id, goods_sn, title, description, content, keywords, 
            price, marketprice, stocks, image, images, spectype, 
            goods_office, goods_trial, goods_kind, pinyin, weigh,
            createtime, updatetime)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        current_time = int(time.time())
        goods_params = (
            category_id, goods['goods_sn'], goods['goods_name'], description,
            content, keywords_str,
            goods['shop_price'], goods['market_price'], goods['goods_number'], 
            goods_thumb, f"{goods_img},{original_img}", 1,
            goods_office, goods_trial, goods_kind, pinyin, 
            goods.get('ranking', 0),
            current_time, current_time
        )
        target_cursor.execute(goods_sql, goods_params)
        target_conn.commit()

        # 获取新商品 ID
        target_cursor.execute("SELECT LAST_INSERT_ID() AS id")
        new_goods_id = target_cursor.fetchone()['id']

        # 处理规格和 SKU
        migrate_sku(goods_id, new_goods_id)

        print(f"已迁移商品: {goods['goods_name']}")

    except Exception as e:
        error_msg = f"迁移商品 ID {goods_id} ({goods['goods_name']}) 时发生错误: {str(e)}"
        logging.error(error_msg)
        print(error_msg)
    finally:
        if target_cursor:
            target_cursor.close()
        if target_conn:
            return_connections(None, target_conn)

def migrate_goods(category_map):
    """迁移商品数据，使用多线程处理"""
    source_conn = None
    source_cursor = None
    try:
        source_conn = get_source_connection()
        source_cursor = source_conn.cursor(pymysql.cursors.DictCursor)
        source_cursor.execute("SELECT * FROM ecs_goods")
        goods_list = source_cursor.fetchall()
        total_goods = len(goods_list)
        print(f"开始迁移 {total_goods} 个商品...")

        # 使用线程池处理商品
        with ThreadPoolExecutor(max_workers=CONFIG['THREAD_POOL']['goods_workers']) as executor:
            futures = [
                executor.submit(process_single_goods, goods, category_map)
                for goods in goods_list
            ]

            # 等待所有任务完成
            for i, future in enumerate(as_completed(futures), 1):
                try:
                    future.result()
                    print(f"进度: {i}/{total_goods}")
                except Exception as e:
                    logging.error(f"商品处理任务失败: {str(e)}")
                    print(f"商品处理任务失败: {str(e)}")

    except Exception as e:
        logging.error(f"迁移商品过程中发生错误: {str(e)}")
        print(f"迁移商品过程中发生错误: {str(e)}")
    finally:
        if source_cursor:
            source_cursor.close()
        if source_conn:
            return_connections(source_conn, None)

def main():
    """主函数"""
    try:
        print("开始数据迁移...")
        
        # 初始化连接池
        init_connection_pools()
        
        # 清空目标表
        clear_target_tables()
        
        # 迁移分类
        category_map = migrate_categories()
        
        # 创建默认分类（ID=999），放在分类迁移之后
        create_default_category()
        
        # 迁移商品
        migrate_goods(category_map)
        
        print("数据迁移完成！")
        
    except Exception as e:
        logging.error(f"迁移过程中发生错误: {str(e)}\n{traceback.format_exc()}")
        print(f"迁移过程中发生错误: {str(e)}")

if __name__ == "__main__":
    main()
